package cn.hdu.goods.book.dao;


import cn.hdu.goods.book.domain.Book;
import cn.hdu.goods.category.domain.Category;
import cn.hdu.goods.pager.Expression;
import cn.hdu.goods.pager.PageBean;
import cn.hdu.goods.pager.PageConstants;
import cn.itcast.commons.CommonUtils;
import cn.itcast.jdbc.TxQueryRunner;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class BookDao {
    private QueryRunner qr =new TxQueryRunner();

    public Book findByBid(String bid) throws SQLException {
        String sql = "select * from t_book WHERE bid=?";
        Map<String,Object> map = qr.query(sql, new MapHandler(), bid);
        Book book = CommonUtils.toBean(map, Book.class);
        Category category = CommonUtils.toBean(map, Category.class);
        book.setCategory(category);
        return book;
    }

    public PageBean<Book> findByCategory(String cid, int pc) throws SQLException {
        List<Expression> exprList = new ArrayList<>();
        exprList.add(new Expression("cid", "=", cid));
        return findByCriteria(exprList, pc);
    }

    public PageBean<Book> findByBname(String bname, int pc) throws SQLException {
        List<Expression> exprList = new ArrayList<>();
        exprList.add(new Expression("bname", "like", "%"+bname+"%"));
        return findByCriteria(exprList, pc);
    }

    public PageBean<Book> findByAuthor(String author, int pc) throws SQLException {
        List<Expression> exprList = new ArrayList<>();
        exprList.add(new Expression("author", "like", "%"+author+"%"));
        return findByCriteria(exprList, pc);
    }

    public PageBean<Book> findByPress(String press, int pc) throws SQLException {
        List<Expression> exprList = new ArrayList<>();
        exprList.add(new Expression("press", "like", "%"+press+"%"));
        return findByCriteria(exprList, pc);
    }

    public PageBean<Book> findByCombination(Book criteria, int pc) throws SQLException {
        List<Expression> exprList = new ArrayList<>();
        exprList.add(new Expression("bname", "like", "%"+criteria.getBname()+"%"));
        exprList.add(new Expression("author", "like", "%"+criteria.getAuthor()+"%"));
        exprList.add(new Expression("press", "like", "%"+criteria.getPress()+"%"));
        return findByCriteria(exprList, pc);
    }


    private PageBean<Book> findByCriteria(List<Expression> exprList, int pc) throws SQLException {

        /**
         * 1.得到ps
         * 2.得到tr
         * 3.得到beanList
         * 4.创建pagebean，返回
         */
        int ps = PageConstants.BOOK_PAGE_SIZE;

        StringBuilder whereSql = new StringBuilder(" where 1=1");
        List<Object> params = new ArrayList<>();//SQL中问号的值
        for (Expression expr : exprList) {
            whereSql.append(" AND ").
                    append(expr.getName()).append(" ").
                    append(expr.getOperator()).append(" ");
            if (!expr.getOperator().equals("is null")) {
                whereSql.append("?");
                params.add(expr.getValue());
            }
        }

        String sql = "SELECT COUNT(*) FROM t_book" + whereSql;
        Number number = qr.query(sql, new ScalarHandler<>(), params.toArray());
        int tr = number.intValue();

        sql = "select * from t_book" + whereSql + " ORDER BY orderBy limit ?,?";
        params.add((pc-1)*ps);
        params.add(ps);
        List<Book> bookList = qr.query(sql, new BeanListHandler<>(Book.class), params.toArray());

        PageBean<Book> pb = new PageBean<>();
        pb.setBeanList(bookList);
        pb.setPc(pc);
        pb.setPs(ps);
        pb.setTr(tr);
        return pb;
    }


}
